package com.itany.ems.impl;

import com.itany.ems.dao.EmpDao;
import com.itany.ems.entity.Emp;
import com.itany.ems.exception.DataAccessException;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {

    public static final String JDBC_DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    public static final String JDBC_URL = "jdbc:mysql://localhost:3306/ems?useUnicode=true&characterEncoding=utf-8";
    public static final String JDBC_USERNAME = "root";
    public static final String JDBC_PASSWORD = "";

    @Override
    public void insertEmp(Emp emp) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(JDBC_DRIVER_CLASS_NAME);
            conn=DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
            String sql="insert into t_emp (name,salary,gender,edu,hobbies) values(?,?,?,?,?)";

            ps=conn.prepareStatement(sql);
            ps.setString(1,emp.getName());
            ps.setDouble(2,emp.getSalary());
            ps.setString(3,emp.getGender());
            ps.setString(4,emp.getEdu());
            ps.setString(5,emp.getHob());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        }
    }

    @Override
    public void deleteEmp(int id) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            Class.forName(JDBC_DRIVER_CLASS_NAME);
            conn=DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
            String sql="delete from t_emp where id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        }
    }

    @Override
    public List<Emp> selectAll() throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Emp emp;
        List<Emp> list=new ArrayList<Emp>();

        try {
            Class.forName(JDBC_DRIVER_CLASS_NAME);
            conn=DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
            String sql="select * from t_emp";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                emp=new Emp();
                emp.setId(rs.getInt(1));
                emp.setName(rs.getString(2));
                emp.setSalary(rs.getDouble(3));
                emp.setGender(rs.getString(4));
                emp.setEdu(rs.getString(5));
                emp.setHob(rs.getString(6));
                list.add(emp);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        }
    }

    @Override
    public void updateEmp(Emp emp) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            Class.forName(JDBC_DRIVER_CLASS_NAME);
            conn=DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
            String sql="update t_emp set name =?,salary=?,gender=?,edu=?,hobbies=? where id=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,emp.getId());
            ps.setString(2, emp.getName());
            ps.setDouble(3,emp.getSalary());
            ps.setString(4,emp.getGender());
            ps.setString(5,emp.getEdu());
            ps.setString(6,emp.getHob());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        }
    }

    @Override
    public void insert(Emp emp) throws DataAccessException {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            Class.forName(JDBC_DRIVER_CLASS_NAME);
            conn=DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
            String sql="insert into t_emp (name,salary,gender,edu,hobbies) values(?,?,?,?,?)";
            ps=conn.prepareStatement(sql);
            ps.setString(1,emp.getName());
            ps.setDouble(2,emp.getSalary());
            ps.setString(3,emp.getGender());
            ps.setString(4,emp.getEdu());
            ps.setString(5,emp.getHob());
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            throw new DataAccessException("数据访问异常");
        }
    }
}
